%Qexcel Tackles the Challenges of Converting Data to SAS from Excel

One common programming task in the pharmaceutical industry is to transfer external data such as lab, ECG, PK, and other non-casereport-form data to SAS from Excel. Spreadsheets can be read with the SAS import wizard or procedures such as PROC ACCESS and PROC IMPORT, but often must be modified by adding or editing column names, formatting cells, deleting blank columns or rows, or moving to different platforms. However, the responsibility of the owner for the contents of the original file is lost when any modification to the file is made. Also the audit trail process required by 21 CFR Part 11 cannot be simplified if such data transfers are made with the non-original spreadsheets. This paper presents a SAS macro, %Qexcel, which has the capability and flexibility to tackle the challenges of handling external data. The macro provides total control over the Excel import with the following features: (1) automatically detecting the worksheet name and cell-range, (2) allowing the user to select any row for data variable names and labels, (3) accepting user-defined variable names and data types (numeric or character), (4) identifying the starting row for the first observation, if not specified, (5) enabling the use of SAS formats and informats to define data fields, (6) changing cell format to 'general' to import the original, unformatted data, (7) transforming out-of-range single cell(s) to be field(s) in a SAS dataset, and (8) uploading the converted SAS data to other platforms (e.g., UNIX) via SAS/CONNECT. This paper, which is suitable for all SAS skill levels, discusses the methodology of %Qexcel design with SAS code, then shows example calls to the macro.